"""
遍历2018、2019、2020三个文件夹，分别将machine、output、rain、seed_area、temperature统计到mysql数据库中。
"""
import os

import pandas as pd
import pymysql


def generate_output():
    # 连接数据库
    db = pymysql.connect(host='localhost', user='root', password='123456', db='wheat_visualize', charset='utf8')
    cursor = db.cursor()

    # 遍历2018、2019、2020三个文件夹
    for year in ['2018', '2019', '2020']:
        # 遍历machine、output、rain、seed_area、temperature文件夹
        for folder in ['output', 'machine', 'rain', 'seed_area', 'temperature']:
            # 遍历文件夹中的xls文件
            for file in os.listdir(os.path.join(year + '/')):
                print(os.path.join(year + '/', file))
                # 读取xls文件
                df = pd.read_excel(os.path.join(file.split('_')[0], year + '_' + folder + ".xls"))
                df = df.fillna(0)
                # 获取列名
                columns = df.columns
                # 获取地区名，地区名是第一列,第一列的所有值
                areas = df.iloc[:, 0].values
                # 遍历地区名
                for area in areas:
                    # 统计每个地区名对应的列名、列名对应的值,拆分出每一个变量的值出来
                    for i in range(1, len(columns)):
                        # 地区名
                        area_name = area
                        # 列名
                        column_name = columns[i]
                        # 列名对应的值
                        column_value = df.iloc[areas.tolist().index(area), i]
                        # machine还是output还是rain还是seed_area还是temperature
                        table_name = folder
                        # 年份
                        year_name = year

                        # 插入数据库
                        print(area_name, column_name, column_value, table_name, year_name)
                        if table_name == 'output':
                            try:
                                sql = "INSERT INTO output_data (place, food_name, output,year) VALUES ('%s', '%s', '%s', '%s')" % (
                                    area_name, column_name, column_value, year_name)
                                cursor.execute(sql)
                                db.commit()
                            except Exception as e:
                                continue
                        elif table_name == 'machine':
                            try:
                                sql = "INSERT INTO machine_data (place, machine_name, value, year) VALUES ('%s', '%s', '%s', '%s')" % (
                                    area_name, column_name, column_value, year_name)
                                cursor.execute(sql)
                                db.commit()
                            except Exception as e:
                                continue

                        elif table_name == 'rain':
                            try:
                                sql = "INSERT INTO rain_data (place, month, value, year) VALUES ('%s', '%s', '%s', '%s')" % (
                                    area_name, column_name, column_value, year_name)
                                cursor.execute(sql)
                                db.commit()
                            except Exception as e:
                                continue

                        elif table_name == 'seed_area':
                            try:
                                sql = "INSERT INTO seed_area_data (place, food_name, value, year) VALUES ('%s', '%s', '%s', '%s')" % (
                                    area_name, column_name, column_value, year_name)
                                cursor.execute(sql)
                                db.commit()
                            except Exception as e:
                                continue

                        elif table_name == 'temperature':
                            try:
                                sql = "INSERT INTO temperature_data (place, month, value, year) VALUES ('%s', '%s', '%s', '%s')" % (
                                    area_name, column_name, column_value, year_name)
                                cursor.execute(sql)
                                db.commit()
                            except Exception as e:
                                continue

                        else:
                            break
